﻿using DocumentFormat.OpenXml.Drawing;
using Learun.Util;
using Learun.Util.SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;

namespace Learun.Application.TwoDevelopment.ZZDT_EC
{
    /// <summary>
    /// 版 本 PIT-ADMS V7.0.3 敏捷开发框架
    /// Copyright (c) 2013-2018 Hexagon PPM
    /// 创 建：超级管理员
    /// 日 期：2022-06-01 15:08
    /// 描 述：数据字典
    /// </summary>
    public class ec_dataitemService
    {
        #region 仓储
        Repository<ec_projectEntity> _projectRepository => new Repository<ec_projectEntity>();
        Repository<ec_dataitemEntity> _dataitemRepository => new Repository<ec_dataitemEntity>();
        Repository<ec_dataitemdetailEntity> _dataitemdetailRepository => new Repository<ec_dataitemdetailEntity>();
        OleDbConnection conn = new OleDbConnection();
        #endregion
        #region 获取数据
        /// <summary>
        /// 获取实体数据
        /// <param name="keyValue">DataItemID or DataItemCode</param>
        /// <summary>
        /// <returns></returns>
        public ec_dataitemEntity GetEntity(string keyValue, string projectId)
        {
            try
            {

                if (string.IsNullOrWhiteSpace(projectId))
                {
                    //return this.BaseRepository().FindEntity<ec_dataitemEntity>(keyValue);
                    return _dataitemRepository.GetById(keyValue);
                }
                else
                {
                    //var ProjectEntity = this.BaseRepository().FindEntity<ec_projectEntity>(projectId);
                    var ProjectEntity = _projectRepository.GetById(projectId);
                    var strSql = new StringBuilder();
                    var res = _dataitemRepository.AsQueryable().AS($"ec_dataitem_{ProjectEntity.ProjectIndex}").Where(x => x.DataItemID == keyValue || x.DataItemCode == keyValue).First();
                    return res;
                }
            }
            catch (Exception ex)
            {
                if (ex is ExceptionEx)
                {
                    throw;
                }
                else
                {
                    throw ExceptionEx.ThrowServiceException(ex);
                }
            }
        }

        /// <summary>
        /// 字典分类列表(即dataitem本身)
        /// </summary>
        /// <returns></returns>
        public IEnumerable<ec_dataitemEntity> GetClassifyList(string projectId)
        {
            try
            {
                IEnumerable<ec_dataitemEntity> res;
                if (string.IsNullOrEmpty(projectId))
                {
                    res = _dataitemRepository.GetList();
                }
                else
                {
                    var tbName = ProjectSugar.TableName<ec_dataitemEntity>(projectId);
                    res = _dataitemRepository.AsQueryable().AS(tbName).OrderBy(x => x.UpdateUserID).OrderBy(x => x.OrderID).ToList();
                }
                return res;
            }
            catch (Exception ex)
            {
                if (ex is ExceptionEx)
                {
                    throw;
                }
                else
                {
                    throw ExceptionEx.ThrowServiceException(ex);
                }
            }
        }

        #endregion

        #region 提交数据

        /// <summary>
        /// 删除实体数据
        /// <param name="keyValue">主键</param>
        /// <summary>
        /// <returns></returns>
        public void DeleteEntity(string keyValue, string projectId)
        {
            //var db = this.BaseRepository().BeginTrans();
            _dataitemRepository.AsTenant().BeginTran();
            try
            {
                if (string.IsNullOrWhiteSpace(projectId))
                {
                    //db.Delete<ec_dataitemEntity>(t => t.DataItemID == keyValue);
                    //db.Delete<ec_dataitemdetailEntity>(t => t.DataItemID == keyValue);
                    _dataitemRepository.Delete(t => t.DataItemID == keyValue);
                    _dataitemdetailRepository.Delete(t => t.DataItemID == keyValue);
                }
                else
                {
                    //var ProjectEntity = this.BaseRepository().FindEntity<ec_projectEntity>(projectId);
                    //db.ExecuteBySql($@" DELETE FROM ec_dataitem_{ProjectEntity.ProjectIndex} WHERE DataItemID = '{keyValue}'; 
                    //                    DELETE FROM ec_dataitemdetail_{ProjectEntity.ProjectIndex} WHERE DataItemID = '{keyValue}';");
                    var ProjectEntity = _projectRepository.GetById(projectId);
                    SqlSugarHelper.Db.Ado.ExecuteCommand($@" DELETE FROM ec_dataitem_{ProjectEntity.ProjectIndex} WHERE DataItemID = '{keyValue}'; 
                                          DELETE FROM ec_dataitemdetail_{ProjectEntity.ProjectIndex} WHERE DataItemID = '{keyValue}';");
                }
                //db.Commit();
                _dataitemRepository.AsTenant().CommitTran();
            }
            catch (Exception ex)
            {
                //db.Rollback();
                _dataitemRepository.AsTenant().RollbackTran();
                if (ex is ExceptionEx)
                {
                    throw;
                }
                else
                {
                    throw ExceptionEx.ThrowServiceException(ex);
                }
            }
        }

        /// <summary>
        /// 保存实体数据（新增、修改）
        /// <param name="keyValue">主键</param>
        /// <summary>
        /// <returns></returns>
        public void SaveEntity(string keyValue, ec_dataitemEntity entity, string projectId)
        {
            //var db = this.BaseRepository().BeginTrans();
            _dataitemRepository.AsTenant().BeginTran();
            try
            {
                if (!string.IsNullOrEmpty(keyValue))
                {
                    //如果code改了，ec property表里的enumdata也需要改。否则ec_objecttypepBLL().GetObjectTypePById会有问题
                    //var oldEntity = this.BaseRepository().FindEntity<ec_dataitemEntity>(keyValue);
                    entity.Modify(keyValue);
                    if (string.IsNullOrWhiteSpace(projectId))
                    {
                        var oldEntity = _dataitemRepository.GetById(keyValue);
                        entity.CreateTime = oldEntity.CreateTime;
                        entity.CreateUserID = oldEntity.CreateUserID;
                        //db.ExecuteBySql($@"UPDATE ec_property set enumdata='{entity.DataItemCode}' where enumdata='{oldEntity.DataItemCode}' and propertytype='00003';");
                        //db.Update(entity);
                        //SqlSugarHelper.Db.Ado.ExecuteCommand($@"UPDATE ec_dataitem set enumdata='{entity.DataItemCode}' where enumdata='{oldEntity.DataItemCode}' and propertytype='00003';");
                        _dataitemRepository.Update(entity);
                    }
                    else
                    {
                        var ProjectEntity = _projectRepository.GetById(projectId);
                        var tableName = $"ec_dataitem_{ProjectEntity.ProjectIndex}";
                        var oldEntity = _dataitemRepository.AsQueryable().AS(tableName).First(x => x.DataItemID == keyValue);

                        //var ProjectEntity = this.BaseRepository().FindEntity<ec_projectEntity>(projectId);
                        //db.ExecuteBySql($@"UPDATE ec_property_{ProjectEntity.ProjectIndex} set enumdata='{entity.DataItemCode}' where enumdata='{oldEntity.DataItemCode}' and propertytype='00003';");
                        //db.UpdateByNoMap(entity, $"ec_dataitem_{ProjectEntity.ProjectIndex}", "DataItemID");

                        //SqlSugarHelper.Db.Ado.ExecuteCommand($@"UPDATE {tableName} set enumdata='{entity.DataItemCode}' where enumdata='{oldEntity.DataItemCode}' and propertytype='00003';");
                        _dataitemRepository.AsUpdateable(entity)
                            .IgnoreColumns(x => x.CreateTime)
                            .IgnoreColumns(x => x.CreateUserID)
                            .AS(tableName).ExecuteCommand();
                    }


                }
                else
                {
                    entity.Create();
                    if (string.IsNullOrWhiteSpace(projectId))
                    {
                        //db.Insert(entity);
                        _dataitemRepository.Insert(entity);
                    }
                    else
                    {
                        //var ProjectEntity = this.BaseRepository().FindEntity<ec_projectEntity>(projectId);
                        //db.InsertByNoMap(entity, $"ec_dataitem_{ProjectEntity.ProjectIndex}");
                        var ProjectEntity = _projectRepository.GetById(projectId);
                        _dataitemRepository.AsInsertable(entity).AS($"ec_dataitem_{ProjectEntity.ProjectIndex}").ExecuteCommand();
                    }
                }
                //db.Commit();
                _dataitemRepository.AsTenant().CommitTran();
            }
            catch (Exception ex)
            {
                //db.Rollback();
                _dataitemRepository.AsTenant().RollbackTran();
                if (ex is ExceptionEx)
                {
                    throw;
                }
                else
                {
                    throw ExceptionEx.ThrowServiceException(ex);
                }
            }
        }

        #endregion

        #region 数据字典明细

        /// <summary>
        /// 获取某一个数据字典的枚举信息
        /// </summary>
        /// <param name="dataItemCode">某一个分类编号<see cref="ec_dataitemEntity.DataItemCode"/></param>
        /// <param name="keyword">关键词（名称/值）</param>
        /// <param name="isAll">是否包含无效数据</param>
        /// <returns></returns>
        public IEnumerable<ec_dataitemdetailEntity> GetDetailList(string parentCode, string detailNameOrCode, string projectId, bool isAll)
        {
            try
            {
                var entity = this.GetEntity(parentCode, projectId);
                if (entity == null)
                {
                    return null;//这样，即使为空。返回是null，而不是【】
                }
                else
                {
                    IEnumerable<ec_dataitemdetailEntity> res;
                    if (string.IsNullOrEmpty(projectId))
                    {
                        res = _dataitemdetailRepository.AsQueryable()
                            .InnerJoin<ec_dataitemEntity>((a, b) => a.DataItemID == b.DataItemID)
                            .ToList();
                    }
                    else
                    {
                        var ProjectEntity = _projectRepository.GetById(projectId);
                        res = _dataitemdetailRepository.AsQueryable().AS($"ec_dataitemdetail_{ProjectEntity.ProjectIndex}")
                           .InnerJoin<ec_dataitemEntity>((a, b) => a.DataItemID == b.DataItemID).AS<ec_dataitemEntity>($"ec_dataitem_{ProjectEntity.ProjectIndex}")
                           .WhereIF(!string.IsNullOrEmpty(parentCode), (a, b) => b.DataItemCode == parentCode)
                           .WhereIF(!isAll, (a, b) => a.IsEnabled == 1)
                           .WhereIF(!string.IsNullOrEmpty(detailNameOrCode), (a, b) => a.DataItemCode == detailNameOrCode || a.DataItemName.Contains(detailNameOrCode))
                           .OrderBy(a => a.OrderID)
                           .OrderBy(a => a.DataItemName)
                           .ToList();
                    } 
                    return res;
                }

            }
            catch (Exception ex)
            {
                if (ex is ExceptionEx)
                {
                    throw;
                }
                else
                {
                    throw ExceptionEx.ThrowServiceException(ex);
                }
            }
        }

        /// <summary>
        /// 删除明细数据
        /// </summary>
        /// <param name="keyValue">主键</param>
        public void DeleteDetail(string keyValue, string projectId)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(projectId))
                {
                    //this.BaseRepository().Delete<ec_dataitemdetailEntity>(x => x.DataItemDetailID == keyValue);
                    _dataitemdetailRepository.Delete(x => x.DataItemDetailID == keyValue);
                }
                else
                {
                    //var ProjectEntity = this.BaseRepository().FindEntity<ec_projectEntity>(projectId);
                    //this.BaseRepository().ExecuteBySql($@" DELETE FROM ec_dataitemdetail_{ProjectEntity.ProjectIndex} WHERE DataItemDetailID = '{keyValue}';");
                    var ProjectEntity = this._projectRepository.GetById(projectId);
                    SqlSugarHelper.Db.Ado.ExecuteCommand($@" DELETE FROM ec_dataitemdetail_{ProjectEntity.ProjectIndex} WHERE DataItemDetailID = '{keyValue}';");
                }
            }
            catch (Exception ex)
            {
                if (ex is ExceptionEx)
                {
                    throw;
                }
                else
                {
                    throw ExceptionEx.ThrowServiceException(ex);
                }
            }
        }

        /// <summary>
        /// 保存明细数据实体
        /// </summary>
        /// <param name="keyValue">主键</param>
        /// <param name="entity">实体</param>
        public void SaveDetailEntity(string keyValue, ec_dataitemdetailEntity entity, string projectId, out string NewID)
        {
            try
            {
                var tableName = ProjectSugar.TableName<ec_dataitemdetailEntity>(projectId);
                //var item = _dataitemdetailRepository.AsQueryable().AS(tableName)
                //    .WhereIF(!string.IsNullOrEmpty(keyValue), x => x.DataItemDetailID != keyValue)
                //    .First(x => x.DataItemName == entity.DataItemName);
                //if (item != null) 
                //{
                //    throw new Exception($"名称不能重复，{entity.DataItemName}已存在");
                //}
                NewID = "";
                if (string.IsNullOrEmpty(keyValue))
                {
                    //yxh，自动给排序
                    if (entity.OrderID == -1)//前台ui是给不出的，是程序在前台的js里故意给的
                    {
                        string sqlstr = "";
                        sqlstr += "select orderid from ec_dataitemdetail";
                        if (string.IsNullOrWhiteSpace(projectId))
                        {

                        }
                        else
                        {
                            //var ProjectEntity = this.BaseRepository().FindEntity<ec_projectEntity>(projectId);
                            var ProjectEntity = _projectRepository.GetById(projectId);
                            sqlstr += "_" + ProjectEntity.ProjectIndex.ToString();
                        }
                        sqlstr += $" where DataItemID = '{entity.DataItemID.ToString()}'";
                        sqlstr += "order by ORDERID desc";
                        //var itemMaxOrder = this.BaseRepository().FindList<ec_dataitemdetailEntity>(sqlstr).FirstOrDefault();
                        var itemMaxOrder = SqlSugarHelper.Db.Ado.SqlQuerySingle<ec_dataitemdetailEntity>(sqlstr);
                        if (itemMaxOrder != null)
                        {
                            var MaxOrderID = itemMaxOrder.OrderID;
                            if (MaxOrderID == null)
                            {
                                entity.OrderID = 1;
                            }
                            else
                            {
                                entity.OrderID = MaxOrderID + 1;
                            }
                        }
                        else
                        {
                            entity.OrderID = 1;
                        }

                    }

                    entity.Create();
                    if (string.IsNullOrWhiteSpace(projectId))
                    {
                        //this.BaseRepository().Insert(entity);
                        _dataitemdetailRepository.Insert(entity);
                    }
                    else
                    {
                        //var ProjectEntity = this.BaseRepository().FindEntity<ec_projectEntity>(projectId);
                        //this.BaseRepository().InsertByNoMap(entity, $"ec_dataitemdetail_{ProjectEntity.ProjectIndex}");
                        var ProjectEntity = _projectRepository.GetById(projectId);
                        _dataitemdetailRepository.AsInsertable(entity).AS($"ec_dataitemdetail_{ProjectEntity.ProjectIndex}").ExecuteCommand();
                    }
                    NewID = entity.DataItemDetailID;
                }
                else
                {
                    entity.Modify(keyValue);
                    if (string.IsNullOrWhiteSpace(projectId))
                    {
                        //this.BaseRepository().Update(entity);
                        _dataitemdetailRepository.Update(entity);
                    }
                    else
                    {
                        //var ProjectEntity = this.BaseRepository().FindEntity<ec_projectEntity>(projectId);
                        //this.BaseRepository().UpdateByNoMap(entity, $"ec_dataitemdetail_{ProjectEntity.ProjectIndex}", "DataItemDetailID");
                        var ProjectEntity = _projectRepository.GetById(projectId);
                        _dataitemdetailRepository.AsUpdateable(entity).AS($"ec_dataitemdetail_{ProjectEntity.ProjectIndex}").ExecuteCommand();
                    }
                    NewID = entity.DataItemDetailID;
                }
            }
            catch (Exception ex)
            {
                if (ex is ExceptionEx)
                {
                    throw;
                }
                else
                {
                    throw ExceptionEx.ThrowServiceException(ex);
                }
            }
        }
        /// <summary>
        /// 导入S3D的PARTNUBMER数据到DATAITEMDEATIL中
        /// </summary>
        /// <param name="strPartNumberItemUID">Part Number节点UID</param>
        /// <param name="strExcelFilePath">EXCEL文件路径</param>
        /// <param name="projectId">项目ID</param>
        public void ImportPartNumberDetails(string strPartNumberItemUID, string strExcelFilePath, string projectId)
        {
            try
            {
                var tblDataItemDetailEntity = ProjectSugar.TableName<ec_dataitemdetailEntity>(projectId);
                //删除表中DataItemID是Part Number ID的数据
                if (string.IsNullOrWhiteSpace(projectId))
                {
                    _dataitemdetailRepository.Delete(x => x.DataItemID == strPartNumberItemUID);
                }
                else
                {
                    var ProjectEntity = this._projectRepository.GetById(projectId);
                    SqlSugarHelper.Db.Ado.ExecuteCommand($@" DELETE FROM ec_dataitemdetail_{ProjectEntity.ProjectIndex} WHERE DataItemID = '{strPartNumberItemUID}';");
                }
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                void InsertEntity(ec_dataitemdetailEntity entity)
                {
                    if (string.IsNullOrWhiteSpace(projectId))
                    {
                        _dataitemdetailRepository.Insert(entity);
                    }
                    else
                    {
                        var ProjectEntity = _projectRepository.GetById(projectId);
                        _dataitemdetailRepository.AsInsertable(entity).AS($"ec_dataitemdetail_{ProjectEntity.ProjectIndex}").ExecuteCommand();
                    }
                }
                //读取EXCEL
                using (var package = new ExcelPackage(strExcelFilePath))
                {
                    //获取工作簿中所有的工作表
                    ExcelWorksheets sheetTablePartNumber = package.Workbook.Worksheets;
                    foreach (ExcelWorksheet ews in sheetTablePartNumber)
                    {
                        if (new[] { "ClassNodeType", "CustomInterfaces", "R-Hierarchy", "GUIDs" }.Contains(ews.Name))
                        {
                            continue;
                        }
                        bool Start = false;
                        int count = 1;
                        for (int i = ews.Dimension.Start.Row; i <= ews.Dimension.End.Row; i++)
                        {
                            for (int j = ews.Dimension.Start.Column; j <= ews.Dimension.End.Column; j++)
                            {
                                if (ews.Cells[i, j].Value==null)
                                {
                                    continue ;
                                }
                                string strCellValue = ews.Cells[i, j].Value.ToString();
                                if (strCellValue == "Start")
                                {
                                    Start = true;
                                    break;
                                }
                                if (strCellValue == "END")
                                {
                                    Start = false;
                                    break;
                                }
                                if (Start && !string.IsNullOrEmpty(strCellValue) && j != 1)
                                {
                                    var entity = new ec_dataitemdetailEntity
                                    {
                                        DataItemID = strPartNumberItemUID,
                                        UpDataItemDetailID = "0",
                                        DataItemName = strCellValue,
                                        DataItemNameEN = string.Empty,
                                        DataItemCode = ews.Cells[i, j + 1]?.Value?.ToString(),
                                        OrderID = count++,
                                        IsEnabled = 1,
                                        Remark = string.Empty
                                    };
                                    entity.Create();
                                    InsertEntity(entity);
                                    break;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion

    }
}
